package com.lu.derby.test;

import cn.hutool.db.*;
import cn.hutool.json.JSONUtil;
import java.sql.*;
import java.util.*;

/**
 * @program derby-demo
 * @description: derby 数据迁移至 mysql
 * @author: zhanglu
 * @create: 2020-06-16 15:59:00
 */
public class JdbcDemo {

    public static void main(String[] args) throws Exception {
//       test01();
        new JdbcDemo().derbyToMysql();
    }

    public static void test01() throws Exception {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");//加载驱动
        Connection conn = DriverManager.getConnection("jdbc:derby:/Users/zhanglu/Desktop/lu/tool/dev/nacos/nacos-server-1.3.0/data/derby-data;");

//        String createTable = "create table sys_user (id varchar(50), name varchar(45))";
//        String queryTable = "select * from sys_user";
//
//        boolean execute = conn.createStatement().execute(createTable);
//        System.out.println(execute);
//
//        boolean execute2 = conn.createStatement().execute("insert into sys_user values('1','测试01')");

        SqlConnRunner sqlConnRunner = DbUtil.newSqlConnRunner(conn);
        List<Entity> all = sqlConnRunner.findAll(conn, "nacos.config_info");

        ResultSet resultSet = conn.createStatement().executeQuery("select * from nacos.config_info");
        while (resultSet.next()){
            String string = resultSet.getString("content");
            System.out.println(string);
        }

//        List<Entity> config_info = sqlConnRunner.findAll(conn, "nacos.config_info");

        System.out.println(1);
    }

    /**
     * derby表 与 mysql表 名称相同，只是数据库名不同
     */
    public void derbyToMysql(){
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            String derbyData = "/Users/zhanglu/Desktop/lu/tool/dev/nacos/nacos-server-1.3.0/data/derby-data;";
            Connection derbyCon = DriverManager.getConnection("jdbc:derby:" + derbyData);

            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection mysqlCon = DriverManager.getConnection("jdbc:mysql://localhost:3306/nacos_config?useSSL=false&serverTimezone=UTC",
                    "root","111111");


            SqlConnRunner derbyRunner = DbUtil.newSqlConnRunner(derbyCon);
            SqlConnRunner mysqlRunner = DbUtil.newSqlConnRunner(mysqlCon);
            List<Entity> all = derbyRunner.findAll(derbyCon, "nacos.config_info");
            mysqlRunner.insert(mysqlCon, all.get(0));

            //derby 需要同步的表
            List<String> tables = Arrays.asList("nacos.config_info", "nacos.config_info_aggr",
                    "nacos.config_info_beta", "nacos.config_info_tag", "nacos.config_tags_relation",
                    "nacos.group_capacity", "nacos.his_config_info", "nacos.permissions",
                    "nacos.roles", "nacos.tenant_capacity", "nacos.tenant_info", "nacos.users");

            derbyToMysql(derbyCon, mysqlCon, tables);

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public void derbyToMysql(Connection derbyCon, Connection mysqlCon, List<String> tables){
        try {
            SqlConnRunner mysqlRunner = DbUtil.newSqlConnRunner(mysqlCon);
            for (String table : tables) {
                ResultSet resultSet = derbyCon.createStatement().executeQuery("select * from  " + table);
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                while (resultSet.next()){
                    String tableName = resultSet.getMetaData().getTableName(1);
                    Entity entity = Entity.create(tableName);
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = resultSet.getMetaData().getColumnName(i);
                        int columnType = resultSet.getMetaData().getColumnType(i);
                        String columnTypeName = resultSet.getMetaData().getColumnTypeName(i);
                        entity.put(columnName, resultSet.getObject(i));
                    }
                    System.out.println(JSONUtil.toJsonStr(entity));
                    try {
                        mysqlRunner.insert(mysqlCon, entity);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}
